package cn.com.jtang.wedding.jdbc;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import cn.com.jtang.wedding.exception.ParameterException;


public class JdbcDao extends JdbcDaoSupport {

	private static final String argIsNull = "参数为null值";
	private static final String canNotFinish = "完成该操作，所需参数不够";

	public JdbcDao() {
	}

	public int insertBySql(final String sql, final List args) {
		checkParameter(sql,args);
		Object[] params = (Object[]) args.toArray(new Object[args.size()]);
		return getJdbcTemplate().update(sql, params);
	}

	public int insert(String table, final List args) {
		checkParameter(table,args);
		if (args.size() <= 0) {
			throw new ParameterException(canNotFinish);
		}
		final StringBuffer sql = new StringBuffer();
		sql.append("insert into ").append(table).append(" values(");
		for (int i = 0; i < args.size(); i++) {
			if (i != 0)
				sql.append(", ");
			sql.append("?");
		}
		sql.append(")");
		Object[] params = (Object[]) args.toArray(new Object[args.size()]);
		return getJdbcTemplate().update(sql.toString(), params);
	}

	public int updateBySql(String sql, List args) {
		checkParameter(args,sql);
		Object[] params = (Object[]) args.toArray(new Object[args.size()]);
		return getJdbcTemplate().update(sql, params);
	}

	public int update(String table, LinkedHashMap<String, Object> map) {
		checkParameter(table,map);
		if (map.size()< 1) {
			throw new ParameterException(canNotFinish);
		}
		StringBuffer sql = new StringBuffer();
		sql.append("update ").append(table).append(" set ");
		int size = map.keySet().size();
		int i = 0;
		for (String key : map.keySet()) {
			if ((i == size - 1) && (size > 1))
				sql.append(" where ").append(key).append(" = ?");
			else {
				if (i != 0)
					sql.append(", ");
				sql.append(key).append(" = ?");
			}
			i++;
		}
		Object[] args = (Object[]) map.values().toArray(new Object[size]);
		return getJdbcTemplate().update(sql.toString(), args);
	}

	public int updateById(String table, Map<String, Object> map, long id) {
		checkParameter(table,map);
		if (map.size()< 1) {
			throw new ParameterException(canNotFinish);
		}
		StringBuffer sql = new StringBuffer();
		sql.append("update ").append(table).append(" set ");
		int size = map.keySet().size();
		int i = 0;
		for (String key : map.keySet()) {
			if (i != 0)
				sql.append(", ");
			sql.append(key).append(" = ?");
			i++;
		}
		sql.append(" where id = ?");
		Object[] args = (Object[]) map.values().toArray(new Object[size + 1]);
		args[size] = id;
		return getJdbcTemplate().update(sql.toString(), args);
	}

	public int deleteBySql(String sql, List args) {
		checkParameter(sql,args);
		Object[] params = (Object[]) args.toArray(new Object[args.size()]);
		return getJdbcTemplate().update(sql, params);
	}

	public int delete(String table, Map<String, Object> map) {
		checkParameter(table);
		StringBuffer sql = new StringBuffer();
		int size = 0;
		Object[] args = new Object[0];
		sql.append("delete from ").append(table);
		if (null != map) {
			size = map.keySet().size();
			if (size == 0) {
				return -1;
			} else {
			    sql.append(" where ");
				int i = 0;
				for (String key : map.keySet()) {
					if (i != 0)
						sql.append(" and ");
					sql.append(key).append(" = ?");
					i++;
				}
				args = (Object[]) map.values().toArray(new Object[size]);
			}
		}
		return getJdbcTemplate().update(sql.toString(), args);
	}

	public int deleteById(String table, long id) {
		checkParameter(table);
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ").append(table).append(" where id = ?");
		Object[] args = new Object[] { id };
		return getJdbcTemplate().update(sql.toString(), args);
	}

	public Object queryBySql(String sql, List args, RowMapper rowMapper) {
		checkParameter(sql,args,rowMapper);
		Object[] params = (Object[]) args.toArray(new Object[args.size()]);
		try {
			return getJdbcTemplate().queryForObject(sql, params, rowMapper);
		} catch (DataAccessException dae) {
			return null;
		}
	}

	public Object queryById(String table, long id, RowMapper rowMapper) {
		checkParameter(table,rowMapper);
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(table).append(" where id = ?");
		Object[] args = new Object[] { id };
		try {
			return getJdbcTemplate().queryForObject(sql.toString(), args,
					rowMapper);
		} catch (DataAccessException dae) {
			return null;
		}
	}

	public Object query(String table, Map<String, Object> map,
			RowMapper rowMapper) {
		checkParameter(table,map,rowMapper);
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(table);
		int size = map.keySet().size();
		if (size > 0) {
			sql.append(" where ");
		}
		int i = 0;
		for (String key : map.keySet()) {
			if (i != 0)
				sql.append(" and ");
			sql.append(key).append(" = ?");
			i++;
		}
		Object[] args = (Object[]) map.values().toArray(new Object[size]);
		try {
			return getJdbcTemplate().queryForObject(sql.toString(), args,
					rowMapper);
		} catch (DataAccessException dae) {
			return null;
		} 
	}

	public Object queryBySql(String sql, List args, Class requiredType) {
		checkParameter(sql,args,requiredType);
		Object[] params = (Object[]) args.toArray(new Object[args.size()]);
		try {
			return getJdbcTemplate().queryForObject(sql, params, requiredType);
		} catch (DataAccessException dae) {
			return null;
		}
	}

	public Object query(String table, Map<String, Object> map, String required,
			Class requiredType) {
		checkParameter(table,map,required,requiredType);
		StringBuffer sql = new StringBuffer();
		sql.append("select ").append(required).append(" from ").append(table);
		int size = map.keySet().size();
		if (size > 0) {
			sql.append(" where ");
		}
		int i = 0;
		for (String key : map.keySet()) {
			if (i != 0)
				sql.append(" and ");
			sql.append(key).append(" = ?");
			i++;
		}
		Object[] args = (Object[]) map.values().toArray(new Object[size]);
		try {
			return getJdbcTemplate().queryForObject(sql.toString(), args,
					requiredType);
		} catch (DataAccessException dae) {
			return null;
		}
	}

	public Object queryById(String table, long id, String required,
			Class requiredType) {
		checkParameter(table,required,requiredType);
		StringBuffer sql = new StringBuffer();
		sql.append("select ").append(required).append(" from ").append(table)
				.append(" where id = ?");
		Object[] args = new Object[] { id };
		try {
			return getJdbcTemplate().queryForObject(sql.toString(), args,
					requiredType);
		} catch (DataAccessException dae) {
			return null;
		}
	}

	public List queryAllBySql(String sql, List args, RowMapper rowMapper) {
		checkParameter(sql,args,rowMapper);
		Object[] params = (Object[]) args.toArray(new Object[args.size()]);
		return getJdbcTemplate().query(sql, params, rowMapper);
	}
	
	public List queryAllBySql(String sql, List args, int start, int end, RowMapper rowMapper) {
		checkParameter(sql,args,rowMapper);
		Object[] params = (Object[]) args.toArray(new Object[args.size()]);
		sql = this.pagination(sql, start, end);
		return getJdbcTemplate().query(sql, params, rowMapper);
	}
	

	public List queryAllByIds(String table, List ids, RowMapper rowMapper,String... orderByStr) {
		checkParameter(table,rowMapper);
		StringBuffer sql = new StringBuffer();
		int size = 0;
		Object[] args = new Object[0];
		sql.append("select * from ").append(table);
		if (null != ids) {
			size = ids.size();
			if (size == 0) {
				return new ArrayList();
			}
			else {
				sql.append(" where ");
				for (int i = 0; i < size; i++) {
					if (i != 0)
						sql.append(" or ");
					sql.append("id = ?");
				}
				args = (Object[]) ids.toArray(new Object[size]);
			}
		}
		if (orderByStr.length ==1) {
			sql.append(" order by ");
			sql.append(orderByStr[0]);
		}
		return getJdbcTemplate().query(sql.toString(), args, rowMapper);
	}

	public List queryAll(String table, Map<String, Object> map,
			RowMapper rowMapper,String... orderByStr) {
		checkParameter(table,rowMapper);
		StringBuffer sql = new StringBuffer();
		Object[] args = new Object[0];
		int size = 0;
		sql.append("select * from ").append(table);
		
		if (null != map) {
			size = map.keySet().size();
			if (size == 0) {
				return new ArrayList();
			}
			else {
				sql.append(" where ");
				int i = 0;
				for (String key : map.keySet()) {
					if (i != 0)
						sql.append(" and ");
					sql.append(key).append(" = ?");
					i++;
				}
				args = (Object[]) map.values().toArray(new Object[size]);
			}
		}
		if (orderByStr.length ==1) {
			sql.append(" order by ");
			sql.append(orderByStr[0]);
		}
		return getJdbcTemplate().query(sql.toString(), args, rowMapper);
	}
	
	public List queryAll(String table, Map<String, Object> map,
			RowMapper rowMapper,int start,int end,String... orderByStr) {
		checkParameter(table,rowMapper);
		StringBuffer sql = new StringBuffer();
		Object[] args = new Object[0];
		int size = 0;
		sql.append("select * from ").append(table);
		
		if (null != map) {
			size = map.keySet().size();
			if (size == 0) {
				return new ArrayList();
			}
			else {
				sql.append(" where ");
				int i = 0;
				for (String key : map.keySet()) {
					if (i != 0)
						sql.append(" and ");
					sql.append(key).append(" = ?");
					i++;
				}
				args = (Object[]) map.values().toArray(new Object[size]);
			}
		}
		if (orderByStr.length ==1) {
			sql.append(" order by ");
			sql.append(orderByStr[0]);
		}
		String sqlStr = this.pagination(sql.toString(), start, end);
		return getJdbcTemplate().query(sqlStr.toString(), args, rowMapper);
	}

	public List queryAllBySql(String sql, List args, Class requiredType) {
		checkParameter(sql,args,requiredType);
		Object[] params = (Object[]) args.toArray(new Object[args.size()]);
		return getJdbcTemplate().queryForList(sql, params, requiredType);
	}
	
	public List queryAllBySql(String sql, List args, Class requiredType, int start, int end) {
		checkParameter(sql,args,requiredType);
		Object[] params = (Object[]) args.toArray(new Object[args.size()]);
		sql = this.pagination(sql, start, end);
		return getJdbcTemplate().queryForList(sql, params, requiredType);
	}

	public List queryAllByIds(String table, List ids, String required,
			Class requiredType,String... orderByStr) {
		checkParameter(table,required,requiredType);
		StringBuffer sql = new StringBuffer();
		Object[] args = new Object[0];
		int size = 0;
		sql.append("select ").append(required).append(" from ").append(table);
		if (null != ids) {
			size = ids.size();
			if (size == 0) {
				return new ArrayList();
			}
			else {
				sql.append(" where ");
				for (int i = 0; i < size; i++) {
					if (i != 0)
						sql.append(" or ");
					sql.append("id = ?");
				}
				args = (Object[]) ids.toArray(new Object[size]);
			}
		}
		if (orderByStr.length ==1) {
			sql.append(" order by ");
			sql.append(orderByStr[0]);
		}
		return getJdbcTemplate().queryForList(sql.toString(), args,
				requiredType);
	}

	public List queryAll(String table, Map<String, Object> map,
			String required, Class requiredType,String... orderByStr) {
		
		checkParameter(table,required,requiredType);
		
		StringBuffer sql = new StringBuffer();
		int size = 0;
		Object[] args = new Object[0];
		sql.append("select ").append(required).append(" from ").append(table);
		if (null != map) {
			size = map.keySet().size();
			if (size == 0) {
				return new ArrayList();
			}
			else {
				sql.append(" where ");
				int i = 0;
				for (String key : map.keySet()) {
					if (i != 0)
						sql.append(" and ");
					sql.append(key).append(" = ?");
					i++;
				}
				args = (Object[]) map.values().toArray(new Object[size]);
			}
		}
		if (orderByStr.length ==1) {
			sql.append(" order by ");
			sql.append(orderByStr[0]);
		}
		return getJdbcTemplate().queryForList(sql.toString(), args,
				requiredType);
	}
	
	public List queryAll(String table, Map<String, Object> map,
			String required, Class requiredType, int start, int end,String... orderByStr) {
		
		checkParameter(table,required,requiredType);
		
		StringBuffer sql = new StringBuffer();
		int size = 0;
		Object[] args = new Object[0];
		sql.append("select ").append(required).append(" from ").append(table);
		if (null != map) {
			size = map.keySet().size();
			if (size == 0) {
				return new ArrayList();
			}
			else {
				sql.append(" where ");
				int i = 0;
				for (String key : map.keySet()) {
					if (i != 0)
						sql.append(" and ");
					sql.append(key).append(" = ?");
					i++;
				}
				args = (Object[]) map.values().toArray(new Object[size]);
			}
		}
		if (orderByStr.length ==1) {
			sql.append(" order by ");
			sql.append(orderByStr[0]);
		}
		
		String sqlStr = this.pagination(sql.toString(), start, end);
		return getJdbcTemplate().queryForList(sqlStr.toString(), args,
				requiredType);
	}
	
	private void checkParameter(Object... objects) {
		for (int i = objects.length-1; i >= 0; i--) {
			if (null == objects[i]) {
				throw new ParameterException(argIsNull);
			}
		}
	}
	
	private String pagination(String sql, int start, int end){
		StringBuffer pagSql = new StringBuffer();

		//MySql数据库
		pagSql.append(sql);
		pagSql.append(" LIMIT "+(start)+","+(end-start));
		
		return pagSql.toString();
	}
	
	public int getCount(String table, Map<String, Object> map){		
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from ").append(table);
		if (null != map) {
			int size = map.keySet().size();
			if (size > 0) {
				sql.append(" where ");
				int i = 0;
				for (String key : map.keySet()) {
					if (i != 0)
						sql.append(" and ");
					sql.append(key).append(" = ").append(map.get(key));
					i++;
				}
			}
		}
		return getJdbcTemplate().queryForInt(sql.toString());

	}
	
	public int updateByKey(String table, Map<String, Object> map, String keyName, String keyValue) {
		checkParameter(table,map);
		if (map.size()< 1) {
			throw new ParameterException(canNotFinish);
		}
		StringBuffer sql = new StringBuffer();
		sql.append("update ").append(table).append(" set ");
		int size = map.keySet().size();
		int i = 0;
		for (String key : map.keySet()) {
			if (i != 0)
				sql.append(", ");
			sql.append(key).append(" = ?");
			i++;
		}
		sql.append(" where ").append(keyName).append(" = ?");
		Object[] args = (Object[]) map.values().toArray(new Object[size + 1]);
		args[size] = keyValue;
		return getJdbcTemplate().update(sql.toString(), args);
	}
	
	public Object queryByKey(String table, String keyName, String keyValue, RowMapper rowMapper) {
		checkParameter(table,rowMapper);
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(table).append(" where ").append(keyName).append(" = ?");
		Object[] args = new Object[] { keyValue };
		try {
			return getJdbcTemplate().queryForObject(sql.toString(), args,
					rowMapper);
		} catch (DataAccessException dae) {
			return null;
		}
	}
	
	public int deleteByKey(String table, String keyName, String keyValue ) {
		checkParameter(table);
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ").append(table).append(" where ").append(keyName).append(" = ?");
		Object[] args = new Object[] { keyValue };
		return getJdbcTemplate().update(sql.toString(), args);
	}
}
